﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.IO;
using System.Data;
using System.Xml;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using System.Transactions;

namespace Servico.Dados
{
    public class SMI
    {
        private SqlConnection ligacao;

        public SMI()
        {
            this.ligacao = null;
        }
        public SMI(String datasource, string db, string user, string password)
        {
            this.ligacao = new SqlConnection("Server=" + datasource + ";Database=" + db + ";User ID=" + user + ";Password=" + password + ";Trusted_Connection=False;");
        }

        public void setLigacao(String datasource, string db, string user, string password) { this.ligacao = new SqlConnection("Server=" + datasource + ";Database=" + db + ";User ID=" + user + ";Password=" + password + ";Trusted_Connection=False;"); }
        public SqlConnection getLigacao() { return this.ligacao; }

        public void connect() { try { this.ligacao.Open(); } catch (SqlException e) { Console.WriteLine(e.Message); } }
        public void disconnect() { try { this.ligacao.Close(); } catch (SqlException e) { Console.WriteLine(e.Message); } }

        public bool testeLigacao()
        {
            if (this.ligacao.State.ToString().Equals("Open"))
            {
                return true;
            }
            else { return false; }
        }



        public bool registarMorada(Negocio.Cliente a)
        {
            SqlCommand mor = new SqlCommand();
            mor.Connection = this.ligacao;
            try
            {
                if (testeLigacao())
                {
                    mor.CommandText = ("INSERT INTO [STAFFSCHED].[dbo].[Moradas]([endereco],[cidade],[distrito],[cod_postal])  VALUES (@endereco,@cidade,@distrito,@cod_postal)");
                    mor.Parameters.AddWithValue("@endereco", a.getEndereco());
                    mor.Parameters.AddWithValue("@cidade", a.getCidade());
                    mor.Parameters.AddWithValue("@distrito", a.getDistrito());
                    mor.Parameters.AddWithValue("@cod_postal", a.getCod_postal());
                    mor.ExecuteNonQuery();
                }
                else return false;
                return true;
            }
            catch (SqlException) { return false; }
        }


        public int getMorada(Negocio.Cliente a)
        {
            SqlCommand qry = new SqlCommand();
            qry.Connection = this.ligacao;
            int id_morada_cli = -1;
            try
            {
                if (testeLigacao())
                {

                    qry.CommandText = "SELECT [id_morada] FROM [STAFFSCHED].[dbo].[Moradas] where endereco='" + a.getEndereco() + "' AND cidade = '" + a.getCidade() + "'	AND distrito='" + a.getDistrito() + "' AND cod_postal='" + a.getCod_postal() + "'";
                    SqlDataReader l = qry.ExecuteReader();
                    while (l.Read())
                    {
                        id_morada_cli = (int)l["id_morada"];
                    }
                    l.Close();

                }
            }
            catch (SqlException) { return -1; }

            return id_morada_cli;
        }


        public int registarMoradaCliente(Negocio.Cliente a)
        {
            SqlCommand mor = new SqlCommand("INSERT INTO [STAFFSCHED].[dbo].[Moradas]([endereco],[cidade],[distrito],[cod_postal])  VALUES ('" + a.getEndereco() + "','" + a.getCidade() + "','" + a.getDistrito() + "','" + a.getCod_postal() + "');", this.ligacao);
            int id_morada = -1;
            try
            {
                if (testeLigacao())
                {
                    mor.ExecuteNonQuery();
                    mor.Parameters.Clear();
                    mor.CommandText = "SELECT @@IDENTITY";
                    id_morada = Convert.ToInt32(mor.ExecuteScalar());
                }
            }
            catch (Exception)
            {
                id_morada = -1;
            }
            return id_morada;
        }

        public bool registarCliente(Negocio.Cliente a, int id_morada_cli)
        {
             SqlCommand cli = new SqlCommand("INSERT INTO [STAFFSCHED].[dbo].[Clientes]([email],[password],[p_nome],[u_nome],[telefone],[morada]) VALUES ('" + a.getEmail() + "','" + a.getPassword() + "','" + a.getPrimeiro_nome() + "','" + a.getUltimo_nome() + "','" + a.getTelefone() + "', " + (id_morada_cli) + ");", this.ligacao);

            try
            {
                if (testeLigacao())
                {
                    cli.ExecuteNonQuery();
                }

                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }


        private bool submeterPedido(Pedido pedido)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.ligacao;
            try
            {
                if (testeLigacao())
                {
                    cmd.CommandText = ("INSERT INTO [STAFFSCHED].[dbo], [Pedidos](([id],[tipo pedido], [id cliente], [data entrada], [prioridade], [alocado], [pronto], [tarefas])VALUES(@id, @tipo pedido, @id cliente, @data entrada, @prioridade, @alocado, @pronto, @tarefas"); //[adiado],     @adiado,
                    cmd.Parameters.AddWithValue("@id", pedido.getId_pedido_recebido());
                    cmd.Parameters.AddWithValue("@tipo pedido", pedido.getId_pedido());
                    cmd.Parameters.AddWithValue("@id cliente", pedido.getId_cliente());
                    cmd.Parameters.AddWithValue("@data entrada", pedido.getData_entrada_pedido());
                    cmd.Parameters.AddWithValue("@prioridade", pedido.getPrioridade());
                    cmd.Parameters.AddWithValue("@alocado", pedido.getAlocado());
                    //cmd.Parameters.AddWithValue("@adiado", pedido.getAdiado());
                    cmd.Parameters.AddWithValue("@pronto", pedido.getPronto());
                    cmd.Parameters.AddWithValue("@tarefas", pedido.getTarefas());
                    cmd.ExecuteNonQuery();
                }
                else { return false; }
                return true;
            }
            catch (SqlException) { return false; }
        }

        //NAO TESTADO

        //public String consultarPlaneamentoGeral(DateTime d){
        //     SqlCommand cmd = new SqlCommand();
        //     string nficheiro = DateTime.Now.ToString();
        //    try
        //    {
        //        if (testeLigacao()) {
        //            var dataSql = d.Date.ToString("yyyy-MM-dd HH:mm:ss");
        //            cmd.CommandText = "select xml from Planeamento where data="+dataSql;
        //            SqlDataReader l = cmd.ExecuteReader();   
        //            byte[] ficheiro = (byte[])cmd.ExecuteScalar();
        //            FileStream fs = new FileStream(nficheiro, FileMode.CreateNew, FileAccess.Write);
        //            fs.Write(ficheiro, 0, ficheiro.Length);
        //            fs.Flush();
        //            fs.Close();
        //        }
        //    }catch (SqlException) {}

        //    StreamReader sr = new StreamReader(nficheiro);
        //    string texto = sr.ReadToEnd();
        //    sr.Close();
        //    return texto;
        //}



        public String consultarPlaneamentoGeral(DateTime d)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.ligacao;
            String texto="";
            d = d.Date;
            try
            {
                if (testeLigacao())
                {
                    cmd.CommandText = "SELECT CAST([xml] AS nvarchar(MAX)) as texto FROM [STAFFSCHED].[dbo].[Planeamento] where data='" + d.ToString() +"'";
                    SqlDataReader l = cmd.ExecuteReader();

                    while (l.Read())
                    {
                        texto = l["texto"].ToString();
                    }
                    l.Close();
                }

            }
            catch (SqlException) { }

            return texto;
        }

        public String consultarPlaneamentoPessoal(String email, DateTime dt)
        {
            String pGeral = consultarPlaneamentoGeral(dt);

            int codTecnico = procurarTecnico(email).getId();

            int pos1 = pGeral.IndexOf("<Tecnico id='" + codTecnico + "'>");
            String aux = pGeral.Substring(pos1);
            int pos2 = aux.IndexOf("</Tecnico>");

            string pPessoal = aux.Substring(0, pos2 + 10);
            return ("<?xml version=\"1.0\" encoding=\"UTF-8\"?><PlaneamentoPessoal>" + pPessoal + "</PlaneamentoPessoal>");
        }

        public bool validarEmailCliente(string email)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.ligacao;
            try
            {
                if (testeLigacao())
                {
                    cmd.CommandText = "SELECT [email] FROM [STAFFSCHED].[dbo].[Clientes] where email='" + email + "'";
                    SqlDataReader l = cmd.ExecuteReader();
                    while (l.Read())
                    {
                        if (l["email"].ToString().ToUpper().Equals(email)) return false;
                    }
                    l.Close();
                }
                return true;
            }
            catch (SqlException) { return false; }
        }

        public bool autenticarCliente(string email, string password)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.ligacao;
            try
            {
                if (testeLigacao())
                {
                    cmd.CommandText = "SELECT [password] FROM [STAFFSCHED].[dbo].[Clientes] where email='" + email + "'";
                    SqlDataReader l = cmd.ExecuteReader();
                    while (l.Read())
                    {
                        if (l["password"].ToString().Equals(password)) return true;
                    }
                    l.Close();
                }
                return false;
            }
            catch (SqlException) { return false; }
        }

        //TODO temos de fazer com que, ao consultarPlaneamento, toma como parametro o dia ANTERIOR a convert(varchar, getdate(), 105)

        public bool registarPlaneamentoGeral(string planeamento)
        {
            string data = DateTime.Now.ToString();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.ligacao;
            try
            {
                if (testeLigacao())
                {
                    cmd.CommandText = ("INSERT INTO [STAFFSCHED].[dbo].[Planeamento] (xml,data) VALUES"
                                       + "(CAST ((@xml) AS nvarchar(MAX)), convert(varchar, getdate(), 105))");
                    //cmd.Parameters.AddWithValue("@data",data);
                    cmd.Parameters.AddWithValue("@xml",planeamento);
                    
                    cmd.ExecuteNonQuery();
                }
                else { return false; }
                return true;
            }
            catch (SqlException) { return false; }
        }


        //Vai buscar os técnicos à Base de Dados
        public Dictionary<int, Tecnico> getTecnicosBD()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.ligacao;
            Dictionary<int, Tecnico> tecnicos = new Dictionary<int, Tecnico>();
            try
            {
                if (testeLigacao())
                {
                    cmd.CommandText = "SELECT [id_funcionario],[nivel] FROM [STAFFSCHED].[dbo].[Funcionarios] WHERE nivel <= 5";
                    SqlDataReader l = cmd.ExecuteReader();
                    while (l.Read())
                    {
                        tecnicos.Add((int)l["id_funcionario"], new Tecnico((int)l["nivel"], (int)l["id_funcionario"], new Dictionary<DateTime, Tarefa>()));
                    }
                    l.Close();
                }
                return tecnicos;
            }
            catch (SqlException) { return null; }
        }

        public int nivelFuncionario(string email)
        {

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.ligacao;
            int i = -1;
            try
            {
                if (testeLigacao())
                {
                    cmd.CommandText = "SELECT [nivel] FROM [STAFFSCHED].[dbo].[Funcionarios] F where F.email='" + email + "'";
                    SqlDataReader l = cmd.ExecuteReader();
                    while (l.Read())
                    {
                        i = (int)l["nivel"];
                    }
                    l.Close();
                }
                return i;
            }
            catch (SqlException) { return i; }
        }

        public bool validarEmailFuncionario(string email)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.ligacao;
            try
            {
                if (testeLigacao())
                {
                    cmd.CommandText = "SELECT [email] FROM [STAFFSCHED].[dbo].[Funcionarios] where email='" + email + "'";
                    SqlDataReader l = cmd.ExecuteReader();
                    while (l.Read())
                    {
                        if (l["email"].ToString().ToUpper().Equals(email)) return false;
                    }
                    l.Close();
                }
                return true;
            }
            catch (SqlException) { return false; }
        }

        // Hash an input string and return the hash as
        // a 32 character hexadecimal string.
        static string getMd5Hash(string input)
        {
            // Create a new instance of the MD5CryptoServiceProvider object.
            MD5 md5Hasher = MD5.Create();

            // Convert the input string to a byte array and compute the hash.
            byte[] data = md5Hasher.ComputeHash(Encoding.Default.GetBytes(input));

            // Create a new Stringbuilder to collect the bytes
            // and create a string.
            StringBuilder sBuilder = new StringBuilder();

            // Loop through each byte of the hashed data 
            // and format each one as a hexadecimal string.
            for (int i = 0; i < data.Length; i++)
            {
                sBuilder.Append(data[i].ToString("x2"));
            }

            // Return the hexadecimal string.
            return sBuilder.ToString();
        }

        // Verify a hash against a string.
        static bool verifyMd5Hash(string input, string hash)
        {
            // Hash the input.
            string hashOfInput = getMd5Hash(input);

            // Create a StringComparer an compare the hashes.
            StringComparer comparer = StringComparer.OrdinalIgnoreCase;

            if (0 == comparer.Compare(hashOfInput, hash))
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool autenticarFuncionario(string email, string password)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.ligacao;
            try
            {
                if (testeLigacao())
                {
                    cmd.CommandText = "SELECT [password] FROM [STAFFSCHED].[dbo].[Funcionarios] where email='" + email + "'";
                    SqlDataReader l = cmd.ExecuteReader();
                    while (l.Read())
                    {
                        if (l["password"].ToString().Equals(password)) return true;
                    }
                    l.Close();
                }
                return false;
            }
            catch (SqlException) { return false; }
        }

        //TODO Quando se fizer o registarTarefasProntas, guarda-se no historico as tarefas prontas e actualiza-se o pedidos recebidos com o nº de tarefas prontas

        //Vai buscar as tarefas à Base de Dados
        public Dictionary<int, Tarefa> getTarefasBD(int id_pedido_recebido)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.ligacao;
            Dictionary<int, Tarefa> tarefas = new Dictionary<int, Tarefa>();
            bool pronta;
            int numTarefas, iteracoes = 0;
            try
            {
                if (testeLigacao())
                {
                    cmd.CommandText = "SELECT [id_tarefa], [id_cliente], [duracao], [precedencia], [nivel_minimo], [n_tarefas_concluidas] FROM [STAFFSCHED].[dbo].[Tarefas] T, [STAFFSCHED].[dbo].[Pedidos_Tarefas] PT, [STAFFSCHED].[dbo].[Pedidos] P, [STAFFSCHED].[dbo].[Pedidos-Recebidos] PR WHERE T.id_tarefa = PT.Tarefasid_tarefa AND P.id_pedido = PT.Pedidosid_pedido AND P.id_pedido = PR.id_pedido AND PR.id_pedidos_recebidos = " + id_pedido_recebido;
                    SqlDataReader l = cmd.ExecuteReader();
                    while (l.Read())
                    {
                        //if ((int)l["id_pedido"] == id_pedido)
                        //{
                        numTarefas = (int)l["n_tarefas_concluidas"] - iteracoes;
                        if (numTarefas > 0) pronta = true;
                        else pronta = false;
                        tarefas.Add((int)l["id_tarefa"], new Tarefa((int)l["id_tarefa"], (int)l["id_cliente"], (int)l["duracao"], (int)l["precedencia"], (int)l["nivel_minimo"], pronta, id_pedido_recebido));
                        iteracoes++;
                        //}
                    }
                    l.Close();
                }
                return tarefas;
            }
            catch (SqlException) { return null; }
        }


        //Vai buscar os pedidos à Base de Dados
        public Dictionary<int, Pedido> getPedidosBD()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.ligacao;
            Dictionary<int, Pedido> pedidos = new Dictionary<int, Pedido>();
            Dictionary<int, Tarefa> tarefas = new Dictionary<int, Tarefa>();
            //int tipoPedido;
            try
            {
                if (testeLigacao())
                {
                    cmd.CommandText = "SELECT [id_pedidos_recebidos], P.id_pedido, [id_cliente], [data_entrada_pedido], [prioridade], [alocado] FROM [STAFFSCHED].[dbo].[Pedidos-Recebidos] PR, [STAFFSCHED].[dbo].[Pedidos] P WHERE PR.id_pedido = P.id_pedido AND PR.acabado = 0 AND PR.cancelado = 0";
                    SqlDataReader l = cmd.ExecuteReader();
                    while (l.Read())
                    {
                        //tipoPedido = (int)l["id_tipo_pedido"];
                        //tarefas = getTarefasBD((int)l["id_pedido"]);
                        //switch (tipoPedido)
                        //{

                        //case (1):
                        //string teste = l["id_cliente"].ToString() + "\n";
                        //System.Console.WriteLine(teste);
                        //TextWriter tw = new StreamWriter("obelo.txt");
                        //tw.WriteLine(teste);
                        //tw.Close();
                        pedidos.Add((int)l["id_pedidos_recebidos"], new Pedido((int)l["id_pedidos_recebidos"], (int)l["id_pedido"], (int)l["id_cliente"], (DateTime)l["data_entrada_pedido"], (int)l["prioridade"], Convert.ToBoolean((int)l["alocado"]), false, new Dictionary<int, Tarefa>()));
                        // break;

                        //    case (2):
                        //        pedidos.Add((int)l["id_pedidos_recebidos"], new Pedido((int)l["id_pedidos_recebidos"], (int)l["id_pedido"], (int)l["id_cliente"], (DateTime)l["data_entrada_pedido"], (int)l["prioridade"], Convert.ToBoolean((int)l["alocado"]), false, tarefas));
                        //        break;

                        //    case (3):
                        //        pedidos.Add((int)l["id_pedidos_recebidos"], new Pedido((int)l["id_pedido_recebido"], (int)l["id_pedido"], (int)l["id_cliente"], (DateTime)l["data_entrada_pedido"], (int)l["prioridade"], Convert.ToBoolean((int)l["alocado"]), false, tarefas));
                        //        break;
                        //}

                    }
                    l.Close();
                    foreach (Pedido pedido in pedidos.Values)
                    {
                        tarefas = new Dictionary<int, Tarefa>();
                        tarefas = getTarefasBD(pedido.getId_pedido_recebido());
                        pedido.setTarefas(tarefas);
                    }
                }
                return pedidos;
            }
            catch (SqlException) { return null; }
        }

        //Recebe um tecnico e devolve o seu nome
        public string getNomeTecnico(Tecnico tecnico)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.ligacao;
            int idTecnico = tecnico.getId();
            string nome = "";
            try
            {
                if (testeLigacao())
                {
                    cmd.CommandText = "SELECT [p_nome] FROM [STAFFSCHED].[dbo].[Funcionarios] WHERE id_funcionario = " + idTecnico;
                    SqlDataReader l = cmd.ExecuteReader();
                    while (l.Read())
                    {
                        nome = l["p_nome"].ToString();
                    }
                    l.Close();
                }
                return nome;
            }
            catch (SqlException) { return ""; }
        }

        //Recebe uma tarefa e devolve o nome do cliente
        public string getNomeCliente(Tarefa tarefa)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.ligacao;
            int idCliente = tarefa.getId_cliente_associado();
            string nome = "";
            try
            {
                if (testeLigacao())
                {
                    cmd.CommandText = "SELECT [p_nome] FROM [STAFFSCHED].[dbo].[Clientes] WHERE id_cliente = " + idCliente;
                    SqlDataReader l = cmd.ExecuteReader();
                    while (l.Read())
                    {
                        nome = l["p_nome"].ToString();
                    }
                    l.Close();
                }
                return nome;
            }
            catch (SqlException) { return ""; }
        }


        //Recebe uma tarefa e devolve a sua designacao
        public string getDesignacaoTarefa(Tarefa tarefa)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.ligacao;
            int idTarefa = tarefa.getId();
            string designacao = "";
            try
            {
                if (testeLigacao())
                {
                    cmd.CommandText = "SELECT [descricao] FROM [STAFFSCHED].[dbo].[Tarefas] WHERE id_tarefa = " + idTarefa;
                    SqlDataReader l = cmd.ExecuteReader();
                    while (l.Read())
                    {
                        designacao = l["descricao"].ToString();
                    }
                    l.Close();
                }
                return designacao;
            }
            catch (SqlException) { return ""; }
        }

        //Recebe uma tarefa e devolve a designacao do pedido a que corresponde
        public string getDesignacaoPedido(Tarefa tarefa)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.ligacao;
            int idTarefa = tarefa.getId();
            string designacao = "";
            try
            {
                if (testeLigacao())
                {
                    cmd.CommandText = "SELECT P.descricao FROM [STAFFSCHED].[dbo].[Tarefas] T, [STAFFSCHED].[dbo].[Pedidos_Tarefas] PT, [STAFFSCHED].[dbo].[Pedidos] P WHERE T.id_tarefa = PT.Tarefasid_tarefa AND PT.Pedidosid_pedido = P.id_pedido AND T.id_tarefa = " + idTarefa;
                    SqlDataReader l = cmd.ExecuteReader();
                    while (l.Read())
                    {
                        designacao = l["descricao"].ToString();
                    }
                    l.Close();
                }
                return designacao;
            }
            catch (SqlException) { return ""; }
        }

        //Recebe uma tarefa e devolve a morada do cliente
        public string getMoradaCliente(Tarefa tarefa)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.ligacao;
            int idCliente = tarefa.getId_cliente_associado();
            string morada = "";
            try
            {
                if (testeLigacao())
                {
                    cmd.CommandText = "SELECT [endereco], [cidade], [distrito], [cod_postal] FROM [STAFFSCHED].[dbo].[Clientes], [STAFFSCHED].[dbo].[Moradas] WHERE id_cliente = " + idCliente + " AND morada = id_morada";
                    SqlDataReader l = cmd.ExecuteReader();
                    while (l.Read())
                    {
                        morada = l["endereco"].ToString() + ", " + l["cidade"].ToString() + ", " + l["cod_postal"].ToString() + ", " + l["distrito"].ToString();
                    }
                    l.Close();
                }
                return morada;
            }
            catch (SqlException) { return ""; }
        }

        public Negocio.Cliente procurarCliente(string email)
        {
            Negocio.Cliente cli = new Negocio.Cliente();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.ligacao;
            try
            {
                if (testeLigacao())
                {
                    cmd.CommandText = "SELECT [id_cliente],[email],[password],[p_nome],[u_nome],[telefone],[endereco] ,[cidade] ,[distrito] ,[cod_postal]" +
                                        "FROM [STAFFSCHED].[dbo].[Clientes],[STAFFSCHED].[dbo].[Moradas] where email='" + email + "' and morada = id_morada";
                    SqlDataReader l = cmd.ExecuteReader();
                    while (l.Read())
                    {
                        cli.setId((int)l["id_cliente"]);
                        cli.setEmail(l["email"].ToString());
                        cli.setPrimeiro_nome(l["p_nome"].ToString());
                        cli.setUltimo_nome(l["u_nome"].ToString());
                        cli.setTelefone(l["telefone"].ToString());
                        cli.setEndereco(l["endereco"].ToString());
                        cli.setCidade(l["cidade"].ToString());
                        cli.setDistrito(l["distrito"].ToString());
                        cli.setCod_postal(l["cod_postal"].ToString());
                    }
                    l.Close();
                }
                return cli;
            }
            catch (SqlException) { return cli; }
        }



        public Tecnico procurarTecnico(string email)
        {
            Tecnico tecnico = new Tecnico();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.ligacao;
            try
            {
                if (testeLigacao())
                {
                    cmd.CommandText = "SELECT [id_funcionario], [nivel] FROM [STAFFSCHED].[dbo].[Funcionarios] where email='" + email + "'";
                    SqlDataReader l = cmd.ExecuteReader();
                    while (l.Read())
                    {
                        tecnico.setId((int)l["id_funcionario"]);
                        tecnico.setNivel((int)l["nivel"]);
                    }
                    l.Close();
                }
                return tecnico;
            }
            catch (SqlException) { return tecnico; }
        }

        public DataTable informacaoInstalacoes(String email) {
            DataTable data=new DataTable("Dados");
            data.TableName = "Dados";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.ligacao;
            data.Columns.Add("id_pedidos_recebidos", typeof(int));
            data.Columns.Add("id_pedido", typeof(int));
            data.Columns.Add("id_cliente", typeof(int));
            data.Columns.Add("data_entrada_pedido", typeof(string));
            data.Columns.Add("data", typeof(string));
      
            try
            {
                if (testeLigacao())
                {
                    for(int i=1;i<4;i++){
                        //cmd.CommandText ="SELECT TOP 1 RP.*, [data] FROM [STAFFSCHED].[dbo].[Historico] H, "+
                        //  " (SELECT TOP 1 [id_pedidos_recebidos],[id_pedido],[id_cliente],[data_entrada_pedido] "+
                        //  " FROM [STAFFSCHED].[dbo].[Pedidos-Recebidos] RP "+
                        //  " WHERE RP.id_cliente=" + procurarCliente(email).getId() + " AND ( RP.id_pedido=" + i + " OR RP.id_pedido=" + (i + 3) + " OR RP.id_pedido=" + (i + 6) + ") AND RP.acabado=1" +
                        //  " ORDER BY RP.id_pedidos_recebidos DESC ) RP "+
                        //    " WHERE H.id_pedidos_recebidos = RP.id_pedidos_recebidos " +
                        //     " ORDER BY H.data DESC;";
                        cmd.CommandText ="SELECT RP.*, max(H.[data]) data" +
                                            " FROM ( "+
                                            " SELECT [id_pedidos_recebidos],[id_pedido],[id_cliente],[data_entrada_pedido] " +
                                            " FROM [STAFFSCHED].[dbo].[Pedidos-Recebidos] RP " +
                                            " WHERE RP.id_cliente=" + procurarCliente(email).getId() + " AND RP.id_pedido="+i+" AND RP.acabado=1 AND RP.cancelado=0) RP " +
                                            " JOIN " +
                                            " (SELECT [id_pedidos_recebidos],[data] " +
                                            " FROM [STAFFSCHED].[dbo].[Historico] ) H " +
                                            " ON RP.id_pedidos_recebidos = H.id_pedidos_recebidos " +
                                            " GROUP BY RP.id_pedidos_recebidos,RP.id_pedido,RP.id_cliente,RP.data_entrada_pedido;";
                        SqlDataReader l = cmd.ExecuteReader();
                    while(l.Read()){
                        data.Rows.Add((int)l["id_pedidos_recebidos"],(int)l["id_pedido"],(int)l["id_cliente"],l["data_entrada_pedido"],l["data"]);
                    }
                    l.Close();
                  }
                }
                return data;
            }
            catch (SqlException) { return null; }
            }

        public bool submeterPedido(string email,int id_pedido_recebidos) { 
               SqlCommand cmd = new SqlCommand();
               cmd.Connection = this.ligacao;
            try
            {
                if (testeLigacao())
                {
                    cmd.CommandText = ("INSERT INTO [STAFFSCHED].[dbo].[Pedidos-Recebidos]([id_pedido],[id_cliente],[data_entrada_pedido],[prioridade],[alocado],[acabado],[cancelado],[n_tarefas_concluidas]) "
                                       + " VALUES (@id_pedido,@id_cliente,@data_entrada_pedido,@prioridade,@alocado,@acabado,@cancelado,@n_tarefas_concluidas)");
                    //cmd.Parameters.AddWithValue("@endereco",a.getEndereco());
                    cmd.Parameters.AddWithValue("@id_pedido",id_pedido_recebidos);
                    cmd.Parameters.AddWithValue("@id_cliente",procurarCliente(email).getId());
                    cmd.Parameters.AddWithValue("@data_entrada_pedido",DateTime.Now);
                    cmd.Parameters.AddWithValue("@prioridade",3);
                    cmd.Parameters.AddWithValue("@alocado",0);
                    cmd.Parameters.AddWithValue("@acabado",0);
                    cmd.Parameters.AddWithValue("@cancelado",0);
                    cmd.Parameters.AddWithValue("@n_tarefas_concluidas",0);
                    cmd.ExecuteNonQuery();
                }
                return true;
            }
            catch (SqlException) { return false; }
        }

        public DataTable tarefasPendentes()
        {
            DataTable view = new DataTable();
            view.TableName = "TarefasPendentesTecnico2";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.ligacao;
            view.Columns.Add("id_pedidos_recebidos", typeof(int));
            view.Columns.Add("id_tarefa", typeof(int));
            view.Columns.Add("id_cliente", typeof(int));
            view.Columns.Add("id_pedido", typeof(int));

            int id_pedido_recebido = -1, old_id_pedido_recebido = -1, n_tarefas_concluidas = 0, i = 1;

            try
            {
                if (testeLigacao())
                {
                    cmd.CommandText = "SELECT TP.*, PR.n_tarefas_concluidas " +
                        "FROM [STAFFSCHED].[dbo].[view_tarefas_pendentes] TP, [STAFFSCHED].[dbo].[Pedidos-Recebidos] PR " +
                        "WHERE TP.id_pedidos_recebidos=PR.id_pedidos_recebidos";
                    //cmd.CommandText = "SELECT * FROM dbo.view_tarefas_pendentes";
                    SqlDataReader l = cmd.ExecuteReader();
                    while (l.Read())
                    {
                        id_pedido_recebido = (int)l["id_pedidos_recebidos"];
                        if (old_id_pedido_recebido != id_pedido_recebido)
                        {
                            i = 1;
                            old_id_pedido_recebido = id_pedido_recebido;
                            n_tarefas_concluidas = (int)l["n_tarefas_concluidas"];
                        }
                        
                        if (i>n_tarefas_concluidas) view.Rows.Add((int)l["id_pedidos_recebidos"], (int)l["id_tarefa"], (int)l["id_cliente"], (int)l["id_pedido"]);
                        i++;
                    }
                    l.Close();

                }
                return view;
            }
            catch (SqlException) { return null; }

        }

        
        public bool registarTarefasRealizadasTecnico(string email, int id_pedidos_recebidos, int id_tarefa, string accao, DateTime hora_fim)
        {
            SqlTransaction atomico = null;
            int n_tarefas_pedido = 0;
            int n_tarefas_concluidas = 0;

            SqlCommand a = new SqlCommand(" SELECT [Expr1] " +
                                    " FROM [STAFFSCHED].[dbo].[view_conta_tarefas_de_pedido] V, " +
                                        " (SELECT P.id_pedidos_recebidos , P.id_pedido " +
                                        " FROM [STAFFSCHED].[dbo].[Pedidos-Recebidos] P " +
                                        " WHERE P.id_pedidos_recebidos = " + id_pedidos_recebidos + ") PR " +
                                    " WHERE V.Pedidosid_pedido = PR.id_pedido;" , this.ligacao);
            SqlCommand b = new SqlCommand("SELECT [n_tarefas_concluidas] FROM [STAFFSCHED].[dbo].[Pedidos-Recebidos]  WHERE [id_pedidos_recebidos] =" + id_pedidos_recebidos + ";" , this.ligacao);

            try
            {
                if (testeLigacao())
                {
                    SqlDataReader l = a.ExecuteReader();
                    while (l.Read())
                    {
                        n_tarefas_pedido = (int)l["Expr1"];
                    }
                    l.Close();


                    SqlDataReader ll = b.ExecuteReader();
                    while (ll.Read())
                    {
                        n_tarefas_concluidas = (int)ll["n_tarefas_concluidas"];
                    }
                    ll.Close();
                }
            }
            catch (Exception)
            {
                return false;
            }


            SqlCommand d = new SqlCommand("UPDATE [STAFFSCHED].[dbo].[Pedidos-Recebidos] SET acabado=1 WHERE [id_pedidos_recebidos]=" + id_pedidos_recebidos + ";", this.ligacao);
            SqlCommand e = new SqlCommand("UPDATE [STAFFSCHED].[dbo].[Pedidos-Recebidos] SET [n_tarefas_concluidas]=" + (n_tarefas_concluidas + 1) + " WHERE [id_pedidos_recebidos]=" + id_pedidos_recebidos + ";", this.ligacao);
            SqlCommand f = new SqlCommand("INSERT INTO [STAFFSCHED].[dbo].[Historico]([id_funcionario],[data],[accao],[id_tarefa],[id_pedidos_recebidos]) "
                                       + " VALUES (" + procurarTecnico(email).getId() + ",'" + hora_fim + "','" + accao + "'," + id_tarefa + "," + id_pedidos_recebidos + ");", this.ligacao);

            // TODO por cancelado = 1 se for um pedido de anulacao e o pedido acabar
            atomico = this.ligacao.BeginTransaction();

            //a.Transaction = atomico;
            //b.Transaction = atomico;
            d.Transaction = atomico;
            e.Transaction = atomico;
            f.Transaction = atomico;
            try
            {
                if (testeLigacao())
                {
                    if (n_tarefas_pedido == n_tarefas_concluidas + 1)
                    {
                        d.ExecuteNonQuery();
                    }

                    e.ExecuteNonQuery();
                    f.ExecuteNonQuery();
                }

                atomico.Commit();
                return true;
            }
            catch (Exception)
            {
                atomico.Rollback();
                return false;
            }


            //try
            //{
            //    if (testeLigacao())
            //    {
            //        a.CommandText = "SELECT [Expr1] " +
            //                        "FROM [STAFFSCHED].[dbo].[view_conta_tarefas_de_pedido] V, " +
            //                            "(SELECT P.id_pedidos_recebidos , P.id_pedido " +
            //                            "FROM [STAFFSCHED].[dbo].[Pedidos-Recebidos] P " +
            //                            "WHERE P.id_pedidos_recebidos = " + id_pedidos_recebidos + ") PR " +
            //                        "WHERE V.Pedidosid_pedido = PR.id_pedido;";
            //        //a.CommandText = "SELECT [Expr1] FROM [STAFFSCHED].[dbo].[view_conta_tarefas_de_pedido] WHERE [Pedidosid_pedido] =" + id_pedidos_recebidos;
            //        SqlDataReader l = a.ExecuteReader();
            //        while (l.Read())
            //        {
            //            n_tarefas_pedido = (int)l["Expr1"];
            //        }
            //        l.Close();

            //        b.CommandText = "SELECT [n_tarefas_concluidas] FROM [STAFFSCHED].[dbo].[Pedidos-Recebidos]  WHERE [id_pedidos_recebidos] =" + id_pedidos_recebidos +";";
            //        SqlDataReader ll = b.ExecuteReader();
            //        while (ll.Read())
            //        {
            //            n_tarefas_concluidas = (int)ll["n_tarefas_concluidas"];
            //        }
            //        ll.Close();

            //        if (n_tarefas_pedido == n_tarefas_concluidas + 1)
            //        {
            //            d.CommandText = "UPDATE [STAFFSCHED].[dbo].[Pedidos-Recebidos] SET acabado=1 WHERE [id_pedidos_recebidos]=" + id_pedidos_recebidos + ";";
            //            d.ExecuteScalar();
            //        }

            //        e.CommandText = "UPDATE [STAFFSCHED].[dbo].[Pedidos-Recebidos] SET [n_tarefas_concluidas]=" + (n_tarefas_concluidas + 1) + " WHERE [id_pedidos_recebidos]=" + id_pedidos_recebidos + ";";
            //        e.ExecuteScalar();

            //        f.CommandText = ("INSERT INTO [STAFFSCHED].[dbo].[Historico]([id_funcionario],[data],[accao],[id_tarefa],[id_pedidos_recebidos]) "
            //                           + " VALUES (@id_funcionario,@data,@accao,@id_tarefa,@id_pedidos_recebidos);");
            //        f.Parameters.AddWithValue("@id_funcionario", procurarTecnico(email).getId());
            //        f.Parameters.AddWithValue("@data", hora_fim);
            //        f.Parameters.AddWithValue("@accao", accao);
            //        f.Parameters.AddWithValue("@id_tarefa", id_tarefa);
            //        f.Parameters.AddWithValue("@id_pedidos_recebidos", id_pedidos_recebidos);
            //        f.ExecuteScalar();

            //        atomico.Commit();
            //        res = true;
            //        //scope.Complete();

            //    }
            //    res = true;// procurarTecnico(email).getId().ToString();
            //}
            //catch (Exception)
            //{ //atomico.Rollback();  
            //    res = false;
            //}
            //return res;
        }

        public DataTable planeamentoToDataTable(string email, DateTime data)
        {
            DataTable tabela = new DataTable();
            tabela.TableName = "TarefasPendentesTecnico1";

            tabela.Columns.Add("id_pedidos_recebidos", typeof(int));
            tabela.Columns.Add("id_tarefa", typeof(int));
            tabela.Columns.Add("designacao_tarefa", typeof(string));
            tabela.Columns.Add("hora_inicio", typeof(DateTime));
            tabela.Columns.Add("hora_fim", typeof(DateTime));

            int id_pedidos_recebidos = 0, id_tarefa = 0;
            string designacao_tarefa = "", help = "";
            DateTime hora_inicio = new DateTime(), hora_fim = new DateTime();
            bool adicionar = false;

            using (StringReader read = new StringReader(consultarPlaneamentoPessoal(email, data)))

            using (XmlReader reader = XmlReader.Create(read))
            {
                while (reader.Read())
                {
                    if (reader.IsStartElement())
                    {
                        switch (reader.Name)
                        {
                            case ("Tarefa"):
                                help = reader["id"];
                                id_tarefa = Convert.ToInt32(help);
                                break;

                            case ("idPedidoRecebido"):
                                if (reader.Read())
                                {
                                    help = reader.Value.Trim();
                                    id_pedidos_recebidos = Convert.ToInt32(help);
                                }
                                break;

                            case ("HoraInicialTarefa"):
                                if (reader.Read())
                                {
                                    help = reader.Value.Trim();
                                    hora_inicio = Convert.ToDateTime(help);
                                }
                                break;

                            case ("HoraFimTarefa"):
                                if (reader.Read())
                                {
                                    help = reader.Value.Trim();
                                    hora_fim = Convert.ToDateTime(help);
                                }
                                break;

                            case ("DesignacaoTarefa"):
                                if (reader.Read())
                                {
                                    designacao_tarefa = reader.Value.Trim();
                                }
                                adicionar = true;
                                break;
                        }
                        if (adicionar)
                        {
                            tabela.Rows.Add(id_pedidos_recebidos, id_tarefa, designacao_tarefa, hora_inicio, hora_fim);
                            adicionar = false;
                        }
                    }
                }
            }
            return tabela;
        }

        public bool verificaExistenciaPlaneamento(DateTime date)
        {
            SqlCommand cmd = new SqlCommand("SELECT [data] FROM [STAFFSCHED].[dbo].[Planeamento] WHERE [data]='"+date+"';", this.ligacao);
            int existe = -1;
            try
            {
                if (testeLigacao())
                {
                    SqlDataReader l = cmd.ExecuteReader();
                    while (l.Read())
                    {
                        existe = DateTime.Compare((DateTime)l["data"], date);
                    }
                    l.Close();
                }
            }
            catch (Exception)
            {
                return false;
            }

            if (existe == 0) return true;
            else return false;
        }


        public void registarPedidos(Dictionary<int, Pedido> pedidos)
        {
            SqlCommand cmd;
            StringBuilder output = new StringBuilder();
            foreach (Pedido pedido in pedidos.Values)
            {
                //output.Append(pedido.getId_pedido_recebido() + ", " + pedido.getAlocado() + ", " + pedido.getPrioridade() + "   ");
                int alocado;
                if (pedido.getAlocado()) alocado = 1;
                else alocado = 0;
                cmd = new SqlCommand("UPDATE [STAFFSCHED].[dbo].[Pedidos-Recebidos] SET [alocado]=" + alocado + ", [prioridade]=" + pedido.getPrioridade() + " WHERE [id_pedidos_recebidos]=" + pedido.getId_pedido_recebido() + ";" , this.ligacao);
                try
                {
                    if (testeLigacao())
                    {
                        cmd.ExecuteNonQuery();
                        
                    }
                }
                catch (Exception){}
                
                
                
                
                //update da prioridade do pedido na BD;
                //update do alocado do pedido na BD;



            }
            //return output.ToString();

            // TODO actualizar prioridade de cada pedido, actualizar alocado + XML
        }

    }
}

   
